package vt.transit.server.data.access;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import vt.transit.server.data.CrimeInstance;
import vt.transit.server.data.schema.CrimeColumns;

public class CrimeInstanceService {

	private static final Logger logger = Logger.getLogger(CrimeInstanceService.class);
	
	/**
	 * Get the number of crimes for a particular metro stop within a specified distance
	 * @param metroStopCode
	 * @param radius
	 * @return
	 */
	public static int getNumberOfCrimes(Connection conn, String metroStopCode, int distance) {
		
		String query = "SELECT count(crime.gid) as crimeCount " +
						"FROM crime, rail " +
						"WHERE ST_DWithin(crime.location::geography, rail.location::geography, " + distance + ") " +
						"AND rail.code='" + metroStopCode + "'";
		int count = 0;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				rs.next();
				count = rs.getInt(1);
			} finally {
				 if (rs != null) {
					 rs.close();
				 }
				 
				 st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 
		
		return count;
	}
	
	public static List<CrimeInstance> getAllCrimes(Connection conn, String metroStopCode, int distance) {		
		String query = "SELECT crime.gid, crime.crime, crime.description, crime.reported, crime.ucr_catagory, crime.ucr_group, crime.street, crime.city, crime.state_code, crime.zip_code, crime.county, ST_AsText(crime.location) as geom " +
						"FROM crime, rail " +
						"WHERE ST_DWithin(crime.location::geography, rail.location::geography, " + distance + ") " +
						"AND rail.code='" + metroStopCode + "'";
		
		List<CrimeInstance> crimeInstances = new ArrayList<CrimeInstance>();
		
		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				while (rs.next()) {
					CrimeInstance crimeInstance = createCrimeInstance(rs);								
					crimeInstances.add(crimeInstance);
				}

			} finally {
				 if (rs != null) {
					 rs.close();
				 }
				 
				 st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 
		
		logger.info(crimeInstances.size());
		return crimeInstances;
	}
	
	/**
	 * Returns all crimes near this lat lon within some distance
	 * @param conn
	 * @param lat
	 * @param lon
	 * @param distance
	 * @return
	 */
	public static List<CrimeInstance> getAllCrimes(Connection conn, double lat, double lon, double distance) {
		String query = "SELECT crime.gid, crime.crime, crime.description, crime.reported, crime.ucr_catagory, crime.ucr_group, crime.street, crime.city, crime.state_code, crime.zip_code, crime.county, ST_AsText(crime.location) as geom " +
						"FROM crime, rail " +
						"WHERE ST_DWithin(crime.location::geography, ST_GeographyFromText('POINT(" + lon + " " + lat + ")'), " + distance + ") ";
		
		List<CrimeInstance> crimeInstances = new ArrayList<CrimeInstance>();
		
		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				while (rs.next()) {
					CrimeInstance crimeInstance = createCrimeInstance(rs);								
					crimeInstances.add(crimeInstance);
				}

			} finally {
				 if (rs != null) {
					 rs.close();
				 }
				 
				 st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 
		
		logger.info(crimeInstances.size());
		return crimeInstances;
	}
	
	/**
	 * Create a CrimeInstance from the current ResultSet
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private static CrimeInstance createCrimeInstance(ResultSet rs) throws SQLException {
		CrimeInstance crimeInstance = new CrimeInstance();
		
		crimeInstance.setGid(rs.getInt(CrimeColumns.gid));
		crimeInstance.setCrime(rs.getString(CrimeColumns.crime));
		crimeInstance.setDescription(rs.getString(CrimeColumns.description));
		crimeInstance.setReported(rs.getString(CrimeColumns.reported));
		crimeInstance.setUcr_catagory(rs.getString(CrimeColumns.ucr_catagory));
		crimeInstance.setUcr_group(rs.getString(CrimeColumns.ucr_group));
		crimeInstance.setStreet(rs.getString(CrimeColumns.street));
		crimeInstance.setCity(rs.getString(CrimeColumns.city));
		crimeInstance.setState_code(rs.getString(CrimeColumns.state_code));
		crimeInstance.setZip_code(rs.getString(CrimeColumns.zip_code));
		crimeInstance.setCounty(rs.getString(CrimeColumns.county));
		crimeInstance.setLocation(rs.getString("geom"));
		
		return crimeInstance;
	}

}
